package com.css.fxfzdzzh.modules.surfaceRupture.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.modules.eqLandslide.count.entity.HistoricalSurfaceEntity;
import com.css.fxfzdzzh.modules.surfaceRupture.entity.*;
import com.css.fxfzdzzh.modules.surfaceRupture.param.PageQueryParam;
import com.css.fxfzdzzh.modules.surfaceRupture.repository.SurfaceRuptureRepository;
import com.css.fxfzdzzh.modules.surfaceRupture.vo.ParamVO;
import com.css.fxfzdzzh.modules.surfaceRupture.vo.SurfaceRuptureNumVO;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author Dbj
 * @date 2023年04月08日  13:49
 */
@Repository
public class SurfaceRuptureRepositoryImpl implements SurfaceRuptureRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Resource
    ElevationMapService elevationMapService;

    @Override
    public void evaluate(List<SurfaceRupture> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql =
        "insert into dzzh_surface_rupture(dsr_id,type,longitude,latitude,earth_quake_level,source_depth,fault_version,fault_properties,"
            + "rupture_length,displacement,rupture_width,length_section,evaluate_user,evaluate_user_name,evaluate_time,data_source,"
            + "longitude_h,longitude_m,longitude_s,latitude_h,latitude_m,latitude_s,province,city,county,province_id,city_id,county_id,del_flag,num,earth_quake_time,source_from,geom) values("
            + ":dsrId, :type, :longitude,:latitude,:earthQuakeLevel,:sourceDepth,:faultVersion, :faultProperties,:ruptureLength,"
            + ":displacement,:ruptureWidth,:lengthSection,:evaluateUser,:evaluateUserName,:evaluateTime,:dataSource,:longitudeH,"
            + ":longitudeM,:longitudeS,:latitudeH,:latitudeM,:latitudeS,:province,:city,:county,:provinceId,:cityId,:countyId,:delFlag,:num,:earthQuakeTime,:sourceFrom,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void updateRuptureLengthAndDisplacement(String drsId,BigDecimal ruptureLength,BigDecimal displacement){
        String sql = "UPDATE dzzh_surface_rupture SET rupture_length=:ruptureLength,displacement=:displacement where dsr_id = :dsr_id";
        Map idMap = new HashMap(3);
        idMap.put("ruptureLength", ruptureLength);
        idMap.put("displacement", displacement);
        idMap.put("dsr_id", drsId);
        namedParameterJdbcTemplate.update(sql.toString(), idMap);
    }

    @Override
    public Map<String, Object> page(PageQueryParam pageQueryParam) {
        StringBuilder sql = new StringBuilder("select dsr_id,num,longitude,latitude,earth_quake_level,source_depth,fault_version,rupture_length,displacement," +
                "CASE WHEN fault_properties = '0' THEN '走滑断层' " +
                "WHEN fault_properties='1' THEN '逆断层'" +
                "WHEN fault_properties='2' THEN '正断层'" +
                "WHEN fault_properties='3' THEN '正走滑'" +
                "else '逆走滑' end as fault_properties" +
                ",concat_ws('',province,city,county) as district,evaluate_user_name,evaluate_time from dzzh_surface_rupture ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder countSql = new StringBuilder("select count(*) from dzzh_surface_rupture ");
        int firstIndex = (pageQueryParam.getPage() - 1) * pageQueryParam.getRows();
        int lastIndex = pageQueryParam.getRows();
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        // 本人评估的结果，本人才能查看和删除; 系统生成的结果，只有国家级管理员可以查看和删除
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE)) {
            whereSql.append(" and (data_source = '2' or data_source = '1') " );
        } else {
            whereSql.append(" and evaluate_user = '").append(userId).append("' ");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getProvince())) {
            whereSql.append(" and province_id = '").append(pageQueryParam.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getCity())) {
            whereSql.append(" and city_id = '").append(pageQueryParam.getCity()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getArea())) {
            whereSql.append(" and county_id = '").append(pageQueryParam.getArea()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getEarthQuakeLevel()) && pageQueryParam.getEarthQuakeLevel()!=null) {
            whereSql.append(" and earth_quake_level = '").append(pageQueryParam.getEarthQuakeLevel()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getFaultProperties()) && pageQueryParam.getFaultProperties()!=null) {
            whereSql.append(" and fault_properties = '").append(pageQueryParam.getFaultProperties()).append("'");
        }
        String sqlWhole = sql.append(whereSql).append(" order by evaluate_time desc ").append(" limit ").append(lastIndex).append(" offset ").append(firstIndex).toString();
        countSql.append(whereSql);
        List<SurfaceRupture> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(SurfaceRupture.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }


    @Override
    public void delete(List<String> idList, Date updateTime) {
        String sql = "UPDATE dzzh_surface_rupture SET del_flag='1',update_user='"+PlatformSessionContext.getUserID()+"',update_time=:updateTime where dsr_id in (:idList)";
        Map idMap = new HashMap(2);
        idMap.put("idList", idList);
        idMap.put("updateTime", updateTime);
        namedParameterJdbcTemplate.update(sql.toString(), idMap);
    }

    @Override
    public void batchSaveLog(List<OperateLog> logs) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(logs.toArray());
        String sql = "INSERT INTO dzzh_surface_rupture_log(dsrdl_id,dsr_id,operate,operate_user,operate_user_name,operate_time) " +
                "values(:dsrdlId,:dsrId,:operate,:operateUser,:operateUserName,:operateTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public String getMaxNum() {
        String sql = "select max(num) from dzzh_surface_rupture where  del_flag = '0'" ;
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public Integer getEarthQuake(BigDecimal longitude, BigDecimal latitude, Date earthQuakeTime,String sourceFrom) {
        String sql = "select count(1) from dzzh_surface_rupture where  longitude = '"+longitude+"' and latitude='"+latitude+"' and earth_quake_time='"+earthQuakeTime+"' and source_from='"+sourceFrom+"'" ;
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<SurfaceRupture> getByNum(String  num){
        String sql = "select * from dzzh_surface_rupture where num='"+num+"' and del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SurfaceRupture.class));
    }

    @Override
    public void updateReportId(List<String> idList, String reportId) {
        String sql = "update dzzh_surface_rupture set report_id=:reportId where dsr_id in (:idList)";
        Map map = new HashMap(2);
        map.put("idList", idList);
        map.put("reportId", reportId);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<SurfaceRuptureNumVO> getRuptureList() {
        StringBuilder sql = new StringBuilder("select distinct num,num as name from dzzh_surface_rupture ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE)) {
            whereSql.append(" and (data_source = '2' or data_source = '1') " );
        } else {
            whereSql.append(" and evaluate_user = '").append(userId).append("' ");
        }
        String sqlWhole = sql.append(whereSql).append(" order by num desc ").toString();
        return jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(SurfaceRuptureNumVO.class));
    }

    @Override
    public void batchSave(List<Fault> faultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(faultList.toArray());
        String sql = "INSERT INTO dzzh_fault(id,scale,fracture_zone_name,dc_name,fault_segment_name,strike_drection,strike,direction,clination,jf_length,top_depth,width,fracture_belt_width,feature,latest_active_period,start_time_est,vdisplace_est,vdisplace_er,hdisplace_est,hdisplace_er," +
                "tdisplace_est,tdisplace_er,ave_vrate,ave_vrate_er,ave_hrate,ave_hrate_rr,start_time_newest,new_vrate,new_vrateer,new_hrate," +
                "new_hrateer,max_vrate,max_vrate_er,max_hrate,max_hrate_rr,eq_event_count,eq_event_ribottom,eq_event_ritop,method," +
                "max_rupture_len,avg_rupture_len,elapse_time_latesteq,slip_depth_est,slip_depth_er,avg_slip_rateest,avg_slip_rateer," +
                "creep_rateest,creep_rateer,cs_max_slipest,cs_max_sliper,cs_ave_slipest,cs_ave_sliper,latest_cs_slipest,latest_cs_sliper," +
                "nsb1,nsb2,nsb3,show_code,active_fault,status,active_period,comment_info,source_flag,del_flag,data_version,create_user," +
                "create_time,update_user,update_time,geom,jf_code,province,city,county,town,extends1,extends2,extends3,extends4,extends5," +
                "extends6,extends7,extends8,extends9,extends10,feature_p,latest_active_period_p,num) values(:id,:scale,:fractureZoneName," +
                ":dcName,:faultSegmentName,:strikeDrection,:strike,:direction,:clination,:jfLength,:topDepth,:width,:fractureBeltWidth," +
                ":feature,:latestActivePeriod,:startTimeEst,:vdisplaceEst,:vdisplaceEr,:hdisplaceEst,:hdisplaceEr,:tdisplaceEst,:tdisplaceEr," +
                ":aveVrate,:aveVrateEr,:aveHrate,:aveHrateRr,:startTimeNewest,:newVrate,:newVrateer,:newHrate,:newHrateer,:maxVrate,:maxVrateEr," +
                ":maxHrate,:maxHrateRr,:eqEventCount,:eqEventRibottom,:eqEventRitop,:method,:maxRuptureLen,:avgRuptureLen,:elapseTimeLatesteq," +
                ":slipDepthEst,:slipDepthEr,:avgSlipRateest,:avgSlipRateer,:creepRateest,:creepRateer,:csMaxSlipest,:csMaxSliper,:csAveSlipest," +
                ":csAveSliper,:latestCsSlipest,:latestCsSliper,:nsb1,:nsb2,:nsb3,:showCode,:activeFault,:status,:activePeriod,:commentInfo," +
                ":sourceFlag,:delFlag,:dataVersion,:createUser,:createTime,:updateUser,:updateTime,ST_GeometryFromText(:geom,4490),:jfCode," +
                ":province,:city,:county,:town,:extends1,:extends2,:extends3,:extends4,:extends5,:extends6,:extends7,:extends8,:extends9," +
                ":extends10,:featureP,:latestActivePeriodP,:num)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void saveMaxLengthFault(MaxLengthFault maxLengthFault) {
    String sql = "insert into dzzh_seismogenic_structure_stimate(dmlf_id,"
            + "fracture_zone_name,"
            + "dc_name,"
            + "fault_segment_name,"
            + "strike_drection,"
            + "strike,"
            + "direction,"
            + "clination,"
            + "jf_length,"
            + "num,data_version,geom) values(:dmlfId, :fractureZoneName, :dcName, :faultSegmentName, :strikeDrection, :strike, :direction , "
            + ":clination, :jfLength, :num, :dataVersion,ST_GeometryFromText(:geom,4490)) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(maxLengthFault);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public Double getFaultLength(String geom) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geom);
        String sql = "select ST_Length(ST_Transform(st_geomfromtext(?,4490),"+epsg+"))";
        return jdbcTemplate.queryForObject(sql, Double.class, geom);
    }

    @Override
    public String getShortestPoint(String geom, String geomText) {
        String sql = "SELECT ST_AsText(ST_ClosestPoint(st_geomfromtext('"+geom+"',4490),st_geometryfromtext('"+geomText+"',4490)))";
        return jdbcTemplate.queryForObject(sql,String.class);
    }

    @Override
    public BigDecimal getShortestDistince(String geomText) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "select ST_Length(ST_Transform(st_geomfromtext(?,4490),"+epsg+"))";
        return jdbcTemplate.queryForObject(sql,  BigDecimal.class, geomText);
    }

    @Override
    public BigDecimal getFaultLength(BigDecimal lon, BigDecimal lat, BigDecimal currentLon, BigDecimal currentLat) {
        String sql = "select ST_Length(ST_Transform(st_geomfromtext('LINESTRING("+lon+" "+lat+","+currentLon+" "+currentLat+")',4490),4527));";
        return jdbcTemplate.queryForObject(sql,  BigDecimal.class);
    }

    @Override
    public String getCenterGeomText(String geom) {
        String sql = "SELECT ST_AsText(ST_centroid(st_geometryfromtext(?,4490)))";
        return jdbcTemplate.queryForObject(sql, String.class, geom);
    }

    @Override
    public String getCenterGeom(String geom) {
        String sql = "SELECT ST_AsText(ST_centroid(?))";
        return jdbcTemplate.queryForObject(sql, String.class, geom);
    }

    @Override
    public void batchSaveFault(List<SurfaceRuptureFault> surfaceRuptureFaultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(surfaceRuptureFaultList.toArray());
        String sql = "INSERT INTO dzzh_surface_rupture_fault(id," +
                "dc_name," +
                "fault_segment_name," +
                "strike_drection," +
                "strike," +
                "direction," +
                "clination," +
                "jf_length," +
                "feature," +
                "pre_fault," +
                "post_fault," +
                "geom," +
                "num," +
                "create_time," +
                "create_user_id,situation,fault_id) values(:id," +
                ":dcName,:faultSegmentName,:strikeDrection,:strike,:direction,:clination,:jfLength," +
                ":feature,:preFault,:postFault,ST_GeometryFromText(:geom,4490),:num," +
                ":createTime,:createUserId,:situation,:faultId)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void batchSaveFaultDisplacement(List<SurfaceRuptureFaultDisplacement> surfaceRuptureFaultDisplacements) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(surfaceRuptureFaultDisplacements.toArray());
        String sql = "INSERT INTO dzzh_surface_rupture_displacement(id," +
                "center_geom," +
                "num,situation,fault_id) values(:id," +
                "ST_GeometryFromText(:centerGeom,4490),:num,:situation,:faultId)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }
    @Override
    public String getGeomText(String geom) {
        String sql = "SELECT ST_AsText(?)";
        return jdbcTemplate.queryForObject(sql, String.class, geom);
    }

    @Override
    public void saveSeismogenicStructureStimate(SeismogenicStructureStimate seismogenicStructureStimate) {
        String sql = "insert into dzzh_seismogenic_structure_stimate("
                + "dsss_id," +
                "longitude," +
                "latitude," +
                "long_axis," +
                "short_axis," +
                "angle," +
                "geom," +
                "num) values(:dsssId, :longitude, :latitude, :longAxis, :shortAxis, :angle, ST_GeometryFromText(:geom,4490) ,:num) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(seismogenicStructureStimate);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public SurfaceRupture getById(String drsId) {
        List<SurfaceRupture> query = jdbcTemplate.query("select * from dzzh_surface_rupture where del_flag='0' and dsr_id='" + drsId + "'", new BeanPropertyRowMapper<>(SurfaceRupture.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):new SurfaceRupture();
    }

    @Override
    public Integer hasEvaluateResult(String num) {
        String sql = "select count(*) from dzzh_surface_rupture_fault where num='"+num+"'";
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    //获取根据地表破裂带评估结果计算发震构造评估椭圆参数
    @Override
    public List<String> getEvaluateResultEllipseParams(String num) {
        List<String> list = new ArrayList<>();
        //查询范围
        String sql1 = "select st_extent(geom) from dzzh_surface_rupture_fault " +
                "where num='"+num+"'";
        String boxExtent = jdbcTemplate.queryForObject(sql1, String.class);
        //地表破裂带空间数据wkt
        String sql2 = "select st_astext(geom) from dzzh_surface_rupture_fault " +
                "where num='"+num+"'";
        String geomText = jdbcTemplate.queryForObject(sql2, String.class);
        //查询范围中心点
        String sql3 = "select st_astext(st_centroid(st_extent(geom))) from dzzh_surface_rupture_fault " +
                "where num='"+num+"'";
        String centerPoint = jdbcTemplate.queryForObject(sql3, String.class);
        if (PlatformObjectUtils.isNotEmpty(boxExtent) && boxExtent.indexOf("BOX(") == 0 &&
                PlatformObjectUtils.isNotEmpty(geomText) && geomText.indexOf("LINESTRING(") != -1 &&
                PlatformObjectUtils.isNotEmpty(centerPoint) && centerPoint.indexOf("POINT(") == 0) {
            //左下角到右上角的连线
            String lineString = boxExtent.replace("BOX","LINESTRING");
            //获取范围经纬度点
            String box = boxExtent.replace("BOX(", "").replace(")", "");
            String[] lonlatArr = box.split(",");
            String[] minLonLat = lonlatArr[0].split(" ");
            String[] maxLonLat = lonlatArr[1].split(" ");
            String minLon = minLonLat[0];
            String minLat = minLonLat[1];
            String maxLon = maxLonLat[0];
            String maxLat = maxLonLat[1];
            //判断空间数据是否包含左下角的点，不包含则使用左上角到右下角的连线
            if(!geomText.contains(lonlatArr[0])){
                String[] testtest=geomText.replace("MULTILINESTRING((","").replace("))","").split(",");
                double xmin=0.0;
                double xmax=0.0;
                double ymin=0.0;
                double ymax=0.0;
                String[] source = testtest[0].split(" ");
                double xsource=Double.parseDouble(source[0]);
                double ysource=Double.parseDouble(source[1]);
                xmin=xsource;
                xmax=xsource;
                ymin=ysource;
                ymax=ysource;

                for(int i=1;i<testtest.length;i++){
                    String[] ss = testtest[i].split(" ");
                    String ss1=ss[0];
                    if(ss1.contains("(")){
                        ss1=ss[0].split("\\(")[1];
                    }
                    double x=Double.parseDouble(ss1);
                    String ss2=ss[1];
                    if(ss2.contains(")")){
                        ss2=ss[1].split("\\)")[0];
                    }
                    double y=Double.parseDouble(ss2);
                    if(ymin>y){
                        ymin=y;
                        xmin=x;
                    }
                    if(ymax<y){
                        ymax=y;
                        xmax=x;
                    }
                }
                if(xmax<xmin) {
                    //左上角
                    String leftTop = minLon + " " + maxLat;
                    //右下角
                    String rightBottom = maxLon + " " + minLat;
                    //左上角到右下角的连线
                    lineString = "LINESTRING(" + leftTop + "," + rightBottom + ")";
                }
            }

            /*if(!geomText.contains(lonlatArr[0])){
                //左上角
                String leftTop = minLon + " " + maxLat;
                //右下角
                String rightBottom = maxLon + " " + minLat;
                //左上角到右下角的连线
                lineString = "LINESTRING(" + leftTop + "," + rightBottom + ")";
            }*/
            //查询空间数据长度（米）
            double length = getGeomLength(lineString,centerPoint);
            String[] centerLonLat = centerPoint.replace("POINT(","").replace(")", "").split(" ");
            list.add(centerLonLat[0]);//中心点经度
            list.add(centerLonLat[1]);//中心点纬度
            //获取长轴（km)
            length = length / 1000;//公里
            list.add(String.valueOf(length));//长轴长度
            list.add(String.valueOf(length/5));//短轴长度：长轴的1/5
            //根据2点经纬度查询连线与正东方向的夹角，[0,180]）
            String[] points = lineString.replace("LINESTRING(","").replace(")", "").split(",");
            String point1 = "POINT(" + points[0] + ")";
            String point2 = "POINT(" + points[1] + ")";
            String angle = getAngleByPoints(point1,point2);
            list.add(angle);
        }
        return list;
    }

    @Override
    public double getGeomLength(String lineString,String centerPoint){
        String sql = "select st_length(st_transform(st_geomfromtext('"+lineString+"',4490)," +
                "(floor((st_x(st_centroid(st_geomfromtext('"+centerPoint+"',4490)))-73.5)/3)-20+4554)::int4))";
        return jdbcTemplate.queryForObject(sql, Double.class);
    }

    //查询P1P2P3夹角,如果angle值大于180则夹角为360-angle，否则夹角为angle，夹角范围为[0,180]
    @Override
    public String queryP1P2P3Angle(String P1LonLat, String P2LonLat, String P3LonLat) {
        String angle = null;
        try {
            if (!PlatformObjectUtils.isEmpty(P1LonLat) && !PlatformObjectUtils.isEmpty(P2LonLat)
                    && !PlatformObjectUtils.isEmpty(P3LonLat)) {
                String sql = "SELECT degrees(ST_Angle(st_geometryfromtext('Point(" + P1LonLat + ")',4490)," +
                        "st_geometryfromtext('Point(" + P2LonLat + ")',4490)," +
                        "st_geometryfromtext('Point(" + P3LonLat + ")',4490)))";
                /*//查询目标点相对于原点的方位角，方位角是从正Y轴(几何图形)或北子午线(地理位置)参考的正顺时针角度
                String sql = "SELECT degrees(ST_Azimuth(st_transform(ST_GeomFromText('Point(" + P2LonLat + ")',4490),2415)," +
                        "st_transform(ST_GeomFromText('Point(" + P3LonLat + ")',4490),2415)))";//投影到平面坐标系*/
                String degrees = jdbcTemplate.queryForObject(sql, String.class);
                if (!PlatformObjectUtils.isEmpty(degrees)) {
                    double angleNumber = Double.parseDouble(degrees);
                    if (angleNumber >= 0 && angleNumber <= 180) {
                        angle = String.valueOf(angleNumber);
                    } else if (angleNumber > 180 && angleNumber < 360) {
                        angleNumber = 360 - angleNumber;
                        angle = String.valueOf(angleNumber);
                    }
                }
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        }

        return angle;
    }

    //根据2点经纬度查询连线与正东方向的夹角，[0,180]
    @Override
    public String getAngleByPoints(String point1,String point2){
        String angle = null;
        if (!PlatformObjectUtils.isEmpty(point1) && !PlatformObjectUtils.isEmpty(point2)
                && point1.contains("POINT(") && point2.contains("POINT(")) {
            String temp1LonLat = point1.replace("POINT(","");
            temp1LonLat = temp1LonLat.replace(")","");
            String[] temp1LonLatArr = temp1LonLat.split(" ");
            String temp2LonLat = point2.replace("POINT(","");
            temp2LonLat = temp2LonLat.replace(")","");
            String[] temp2LonLatArr = temp2LonLat.split(" ");
            if(temp1LonLatArr.length==2 && temp2LonLatArr.length==2){
                double temp1Lon = Double.parseDouble(temp1LonLatArr[0]);
                double temp1Lat = Double.parseDouble(temp1LonLatArr[1]);
                double temp2Lon = Double.parseDouble(temp2LonLatArr[0]);
                double temp2Lat = Double.parseDouble(temp2LonLatArr[1]);
                /*//取经度小的为P2，经度大的为P3，P1为P2同经度且纬度增加1的点
                if (temp1Lon > temp2Lon) {
                    String P2LonLat = temp2LonLat;
                    String P3LonLat = temp1LonLat;
                    //P1为P2同经度且纬度增加1的点
                    String P1LonLat = temp2Lon + " " + (temp2Lat + 1);
                    //查询P1P2P3夹角,如果angle值大于180则夹角为360-angle，否则夹角为angle，夹角范围为[0,180]
                    angle = queryP1P2P3Angle(P1LonLat, P2LonLat, P3LonLat);
                } else {
                    String P2LonLat = temp1LonLat;
                    String P3LonLat = temp2LonLat;
                    //P1为P2同经度且纬度增加1的点
                    String P1LonLat = temp1Lon + " " + (temp1Lat + 1);
                    //查询P1P2P3夹角,如果angle值大于180则夹角为360-angle，否则夹角为angle，夹角范围为[0,180]
                    angle = queryP1P2P3Angle(P1LonLat, P2LonLat, P3LonLat);
                }*/
                //取纬度小的为P2
                if (temp1Lat > temp2Lat) {
                    String P1LonLat = temp1LonLat;
                    String P2LonLat = temp2LonLat;
                    //P3为P2同纬度，经度增加1度的点
                    String P3LonLat = (temp2Lon + 1) + " " + temp2Lat;
                    //查询P1P2P3夹角,如果angle值大于180则夹角为360-angle，否则夹角为angle，夹角范围为[0,180]
                    angle = queryP1P2P3Angle(P1LonLat, P2LonLat, P3LonLat);
                } else {
                    String P1LonLat = temp2LonLat;
                    String P2LonLat = temp1LonLat;
                    //P3为P2同纬度，经度增加1度的点
                    String P3LonLat = (temp1Lon + 1) + " " + temp1Lat;
                    //查询P1P2P3夹角,如果angle值大于180则夹角为360-angle，否则夹角为angle，夹角范围为[0,180]
                    angle = queryP1P2P3Angle(P1LonLat, P2LonLat, P3LonLat);
                }
            }
        }

        return angle;
    }

    @Override
    public List<SurfaceRupture> getSurfaceRuptureNumOfPg() {
        String sql = "select province,city,county,dsr_id,longitude,latitude from dzzh_surface_rupture where del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SurfaceRupture.class));
    }

    @Override
    public void saveParam(ParamVO paramVO) {
        String sql = "insert into dzzh_surface_rupture_param(id,projection_point_distance,slope_distance,earth_quake8_width,earth_quake758_width,earth_quake775_width,earth_quake657_width,user_id,save_time)" +
                " values(:id,:projectionPointDistance,:slopeDistance,:earthQuake8Width,:earthQuake758Width,:earthQuake775Width,:earthQuake657Width,:userId,:saveTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(paramVO);
        namedParameterJdbcTemplate.update(sql, source);
    }


    @Override
    public void deleteParam() {
        jdbcTemplate.update("delete from dzzh_surface_rupture_param");
    }

    @Override
    public ParamVO getParam() {
        List<ParamVO> list = jdbcTemplate.query("select * from dzzh_surface_rupture_param", new BeanPropertyRowMapper<>(ParamVO.class));
        return PlatformObjectUtils.isNotEmpty(list)&& list.size()>0?list.get(0):null;
    }

    @Override
    public void save(SurfaceRupture entity) {
        jdbcTemplate.update("insert into dzzh_surface_rupture  (dsr_id,province,longitude,latitude,rupture_length ,earth_quake_level,earth_quake_time,geom)" +
                " values ('" + entity.getDsrId() + "', '" + entity.getProvince() + "', '" + entity.getLongitude() + "', '" + entity.getLatitude() + "', '" + entity.getRuptureLength() + "', '" + entity.getEarthQuakeLevel() + "', '" + entity.getEarthQuakeTime() + "',st_geomfromtext('POINT(' || " + entity.getLongitude() + " || ' ' || " + entity.getLatitude() + " || ')', 4490)) ");
    }


}
